package com.liuyonggang.generator.util;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;

import com.liuyonggang.generator.model.Column;
import com.liuyonggang.generator.model.Database;
import com.liuyonggang.generator.model.Table;
import com.mysql.jdbc.PreparedStatement;

/**
 * 数据库信息获得操作类
 */
public class DatabaseInfoOp {

	Connection conn = null;

	private String dataType;
	private String classDriver;
	private String url;
	private String username;
	private String password;
	@SuppressWarnings("unused")
	private String schema;

	public DatabaseInfoOp(String classDriver, String url, String username, String password) {
		super();
		this.classDriver = classDriver;
		this.url = url;
		this.username = username;
		this.password = password;
	}
	
	public DatabaseInfoOp(String classDriver, String url, String username, String password, String schema) {
		super();
		this.classDriver = classDriver;
		this.url = url;
		this.username = username;
		this.password = password;
		this.schema = schema;
	}

	public Connection getConnectionByJDBC() {
		try {
			// 装载驱动包类
			Class.forName(classDriver);
			
			// 加载驱动
			Properties props =new Properties();
			props.put("remarksReporting","true");
			props.put("user", username); 
			props.put("password", password);
			conn = DriverManager.getConnection(url,props);
			
		} catch (ClassNotFoundException e) {
			System.out.println("装载驱动包出现异常!请查正！");
			e.printStackTrace();
		} catch (SQLException e) {
			System.out.println("链接数据库发生异常!");
			e.printStackTrace();
		}
		return conn;
	}

	public String getTableRemark(String tableName){
		String remark="";
		String sql = "SELECT TABLE_NAME, TABLE_COMMENT FROM information_schema.tables where TABLE_NAME='"+tableName+"'"; 
		
		try {
			PreparedStatement ps = (PreparedStatement) conn.prepareStatement(sql);  
			ResultSet rs = ps.executeQuery();
			while(rs.next()) {
			    remark=rs.getString("TABLE_COMMENT");
			   }
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		return remark;
	}


	public Database getDbInfo() throws SQLException {

		Database databaseBean = new Database();

		// 表队列
		List<Table> tableList = new ArrayList<Table>();

		// 初始化数据库
		getConnectionByJDBC();
		
		// 获取数据库信息
		DatabaseMetaData dbmd = conn.getMetaData();
		dataType=dbmd.getDatabaseProductName().toLowerCase();
		databaseBean.setDatabaseProductName(dbmd.getDatabaseProductName());

		if("mysql".equals(dataType)){

		   /**获得数据库表
			* 功能描述：得到指定参数的表信息
			* 参数说明：
			* 参数:catalog:目录名称，一般都为空.
			* 参数：schema:数据库名，对于oracle来说就用户名
			* 参数：tablename:表名称
			* 参数：type :表的类型(TABLE | VIEW)
			* 注意：在使用过程中，参数名称必须使用大写的。否则得到什么东西。
			*/
//			ResultSet rs = dbmd.getTables(null, this.schema, null, new String[] { "TABLE", "VIEW" });
			ResultSet rs = dbmd.getTables(null, "%", "%", new String[] { "TABLE", "VIEW" });

			// String tableName = "";
			while (rs.next()) {
				Table table = new Table();
				
				table.setTableName(rs.getString("TABLE_NAME"));
//				table.setTableComment(rs.getString("REMARKS"));
				table.setTableComment(getTableRemark(rs.getString("TABLE_NAME")));
				table.setTableSchem(rs.getString(1));

				// 设置列信息
				ResultSet rscol = dbmd.getColumns(null, null, table.getTableName(), null);

				Column tempColumn;

				while (rscol.next()) {
					tempColumn = new Column();
					tempColumn.setColumnName(rscol.getString("COLUMN_NAME"));
					//tempColumn.setColumnType(Integer.parseInt(rscol.getString("DATA_TYPE")));
					tempColumn.setColumnType(rscol.getString("TYPE_NAME"));

					String remarks = rscol.getString("REMARKS");
					if (remarks.length() < 1)
						remarks = "";
					tempColumn.setColumnComment(remarks);
					tempColumn.setAutoIncrement(rscol.getString("IS_AUTOINCREMENT").equals("YES"));
					tempColumn.setNullAble(rscol.getString("IS_AUTOINCREMENT").equals("YES"));

					// 添加列到表中
					table.getColumnList().add(tempColumn);

				}

				// 设置主键列
				ResultSet rsPrimary = dbmd.getPrimaryKeys(null, null, table.getTableName());
				while (rsPrimary.next()) {
					if (rsPrimary.getString("COLUMN_NAME") != null) {

						for (int i = 0; i < table.getColumnList().size(); i++) {
							Column coltemp = table.getColumnList().get(i);
							if (coltemp.getColumnName().equals(rsPrimary.getString("COLUMN_NAME"))) {
								coltemp.setPrimary(true);
							}
						}

					}
				}

				// 设置外键列
				ResultSet rsFPrimary = dbmd.getImportedKeys(null, null, table.getTableName());
				while (rsFPrimary.next()) {

					for (int i = 0; i < table.getColumnList().size(); i++) {
						Column coltemp = table.getColumnList().get(i);
						if (coltemp.getColumnName().equals(rsFPrimary.getString("FKCOLUMN_NAME"))) {
							//System.out.println("FKCOLUMN_NAME "+rsFPrimary.getString("FKCOLUMN_NAME"));
							coltemp.setForeignKey(true);
						}
					}
				}
				
				
				
				
				
				
				
				tableList.add(table);
			}
		}else{

			// 获得数据库表
//			ResultSet rs = dbmd.getTables(null, null, CodeGenerator.prefix.toUpperCase(), new String[] { "TABLE" , "VIEW"});
			ResultSet rs = dbmd.getTables(null, null, "%", new String[] { "TABLE" , "VIEW"});

			while (rs.next()) {
				Table table = new Table();
//				System.out.println("REMARKS "+rs.getString("REMARKS"));

				table.setTableName(rs.getString("TABLE_NAME"));
				table.setTableComment(rs.getString("REMARKS"));
				table.setTableSchem(rs.getString(1));

				// 设置列信息
				ResultSet rscol = dbmd.getColumns(null, null, table.getTableName(), null);
				
				while (rscol.next()) {
					
					Column tempColumn = new Column();
					tempColumn.setColumnName(rscol.getString("COLUMN_NAME"));
					tempColumn.setColumnType(rscol.getString("TYPE_NAME"));

					String remarks = rscol.getString("REMARKS");
					if(null == remarks)
						remarks = "";
					tempColumn.setColumnComment(remarks);
					
					// 添加列到表中
					table.getColumnList().add(tempColumn);
					
				}
	            
	            tableList.add(table);
	            
			}
			
            
		}

		databaseBean.setTableList(tableList);
		return databaseBean;
	}

	public String getDbTableRemark() {
		try {
			getConnectionByJDBC();
			
			DatabaseMetaData dbmd = this.conn.getMetaData();
			ResultSet rs = dbmd.getTables(null, null, CodeGenerator.prefix.toUpperCase(), new String[]{"TABLE","VIEW"});
			
			if(rs.next()){
				return rs.getString("REMARKS");
			}
			
		} catch (Exception e) {
			e.printStackTrace();
		}
		return "";
	}
}
